package com.vcs.frm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.vcs.frm.dao.mappers.CityRowMapper;
import com.vcs.frm.dao.mappers.DesignationRowMapper;
import com.vcs.frm.dao.mappers.NoticeBoardRowMapper;
import com.vcs.frm.dao.mappers.StateRowMapper;
import com.vcs.frm.dao.mappers.UserResultSetExtractor;
import com.vcs.frm.domain.City;
import com.vcs.frm.domain.Designation;
import com.vcs.frm.domain.NoticeBoard;
import com.vcs.frm.domain.State;
import com.vcs.frm.domain.User;

public class CommonDaoImpl implements CommonDao {

	Log log = LogFactory.getLog(this.getClass());

	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedTemplate;

	private String getNoticeBoardsQuery;
	private String updateNoticeBoardQuery;

	public CommonDaoImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		namedTemplate = new NamedParameterJdbcTemplate(dataSource);
	}

	public void setGetNoticeBoardsQuery(String getNoticeBoardsQuery) {
		this.getNoticeBoardsQuery = getNoticeBoardsQuery;
	}

	public void setUpdateNoticeBoardQuery(String updateNoticeBoardQuery) {
		this.updateNoticeBoardQuery = updateNoticeBoardQuery;
	}

	@Override
	public List<State> getAllStates() {

		String qry = "SELECT S.STATE_ID, S.STATE_NAME FROM MST_STATES S";

		List<State> states = (List<State>) jdbcTemplate.query(qry,
				new StateRowMapper());
		System.out.println(states);
		return states;
	}

	@Override
	public List<City> getCitiesByState(int stateId) {
		List<City> cities = null;
		String sql = "SELECT C.CITY_ID, C.CITY_NAME, C.STATE_ID FROM MST_CITIES C WHERE C.STATE_ID = "
				+ stateId;
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("stateId", stateId);
		cities = (List<City>) jdbcTemplate.query(sql, new CityRowMapper());
		return cities;
	}

	@Override
	public List<Designation> getDesignationList() {
		log.info("getDesignationList...................");
		List<Designation> designationList = new ArrayList<Designation>();
		String sql = "SELECT D.DESIGNATION_CODE, D.DESIGNATION FROM DESIGNATIONS D";
		designationList = jdbcTemplate.query(sql, new DesignationRowMapper());
		System.out.println("designationList size : " + designationList.size());
		return designationList;
	}

	@Override
	public NoticeBoard getNoticeBoard() {
		NoticeBoard nb = (NoticeBoard) jdbcTemplate.queryForObject(
				getNoticeBoardsQuery, new NoticeBoardRowMapper());
		return nb;
	}

	@Override
	public int updateNoticeBoard(NoticeBoard noticeBoard) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("text", noticeBoard.getNoticeBoardText());
		params.put("updatedDate", noticeBoard.getUpdatedDate());
		params.put("updatedBy", noticeBoard.getUpdatedBy());

		int status = namedTemplate.update(updateNoticeBoardQuery, params);
		return status;
	}

	/**
	 * Get Enumeration constants
	 */
	public List<String> getEnums(String enumGroup) {
		String sql = "SELECT * FROM ENUMS E WHERE upper(E.ENUM_GROUP) like uppper(':enumGroup')";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("enumGroup", enumGroup);
		List<String> enums = namedTemplate.query(sql, params,
				new RowMapper<String>() {

					@Override
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("ENUM_VALUE");
					}
				});
		return enums;
	}

	@Override
	public User getUser(int userId) {

		String sql = "SELECT * FROM USERS U JOIN USER_ROLES R ON U.USER_ID= R.USER_ID WHERE U.USER_ID= :userId";

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", userId);
		User user = new User();
		user = (User) namedTemplate.query(sql, params,
				new UserResultSetExtractor());
		// System.out.println(">>>>>>>>>>>>>>>>>"+ user );
		return user;
	}

	/**
	 * Inserts record in USERS table and also sets the assigned roles in USER_ROLES table.
	 * This method should be called from service layer inside a transaction.
	 */
	
	@Override
	public int createUserAndSetRoles(String username, List<String> authorities) {

		// STEP 1: CREATE USER AND GET IT'S ID BACK FOR INSERTING IN USER_ROLES AND TEACHERS TABLE.
		String sql = "INSERT INTO USERS(USERNAME, PASSWORD, ENABLED) VALUES (:username, :password,:enabled)";

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("username", username);
		params.put("password", "pass123");
		params.put("enabled", 1);

		// this will hold the generate key
		KeyHolder keyHolder = new GeneratedKeyHolder();
		// parameters source
		SqlParameterSource paramsSource = new MapSqlParameterSource(params);

		int status = namedTemplate.update(sql, paramsSource, keyHolder);
		System.out.println("||||||||| : " + keyHolder.getKey());

		// STEP 2: create user_roles for user
		for (String role : authorities) {

			String roleSql = "INSERT INTO USER_ROLES(USER_ID, AUTHORITY) VALUES (:userId, :authority)";
			Map<String, Object> args = new HashMap<String, Object>();
			args.put("userId", keyHolder.getKey().intValue());
			args.put("authority", role);
			namedTemplate.update(roleSql, args);
		}

		return keyHolder.getKey().intValue();
	}

}
